package demo.orm;

import com.alibaba.fastjson.JSON;
import lombok.Data;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.lang.reflect.*;
import java.sql.*;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @author : GuoFei
 * @date : 2019/1/22 13:55
 */
@Data
@Service
public class JDBCUtils {

    private static Logger log = LoggerFactory.getLogger(JDBCUtils.class);

    private static void handleParams(PreparedStatement ps, Object... params) {
        Object param;
        if (params != null) {
            for (int i = 0; i < params.length; i++) {
                try {
                    param = params[i];
                    if (param instanceof java.util.Date) {
                        param = new Timestamp(((Date) param).getTime());
                    }
                    ps.setObject(i + 1, param);
                } catch (Exception e) {
                    e.printStackTrace();
                    log.error("参数填充有误");
                }
            }
        }
    }

    private static int executeDML(String sql, Object... params) {
        Connection connection = DBManage.getConn();
        if (connection == null) {
            log.error("数据库链接获取有误");
            return 0;
        }
        int count = 0;
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            handleParams(ps, params);
            count = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            log.error("sql语句拼接有误");
        }
        return count;
    }

    private static Object getFieldValue(String field, Object obj) {
        Class clazz = obj.getClass();
        Field idField;
        Object id = null;
        try {
            idField = clazz.getDeclaredField(field);
            idField.setAccessible(true);
            id = idField.get(obj);
        } catch (NoSuchFieldException | IllegalAccessException e) {
            e.printStackTrace();
        }
        return id;
    }

    public static void insert(Object obj) {
        Class clazz = obj.getClass();
        TableInfo tableInfo = TableContext.poClassTableMap(obj);
        log.info(tableInfo.getOnlyPriKey().getName());
        log.info("新增数据对应表信息：" + JSON.toJSONString(tableInfo));
        StringBuilder sb = new StringBuilder();
        sb.append("insert into ").append(tableInfo.getTableName()).append("(");
        Field[] fields = clazz.getDeclaredFields();
        Object[] fieldValue = new Object[fields.length-1];
        int index = 0;
        for (Field field : fields) {
            if (!field.getName().equals(tableInfo.getOnlyPriKey().getName())) {
                fieldValue[index++] = getFieldValue(field.getName(), obj);
                sb.append(StringUtils.camel2Underline(field.getName())).append(",");
            }
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        sb.append(" values(");
        for (int i = 0; i < fields.length - 1; i++) {
            sb.append("?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(")");
        log.info("params:【" + JSON.toJSONString(fieldValue) + "】");
        log.info("insert:【" + sb.toString() + "】");
        executeDML(sb.toString(), fieldValue);
    }

    /**
     * 返回一个值（一行一列）
     * @param sql   查询语句
     * @param params    sql的参数
     * @return  查询到的结果
     */
    public static Object querySingleObject(String sql, Object... params){
        Connection connection=DBManage.getConn();
        ResultSet resultSet=null;
        try {
            PreparedStatement ps=connection.prepareStatement(sql);
            handleParams(ps,params);
            log.info("params:【" + JSON.toJSONString(params) + "】");
            log.info("insert:【" + sql + "】");
            resultSet=ps.executeQuery();
            try {
                while (resultSet.next()){
                    //只返回第一条记录的第一个字段的值
                    return resultSet.getObject(1);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }finally {
                DBManage.close(ps,connection);
            }
            return null;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 返回一行记录，并将一行记录封装到指定的类中
     * @param sql   查询语句
     * @param clazz 封装数据的javabean类的Class对象
     * @param params    sql的参数
     * @return  查询到的结果
     */
    public Object queryObject(String sql, Class clazz, Object... params){
        List result=queryList(sql,clazz,params);
        if (result!=null&&result.size()>0){
            return result.get(0);
        }
        return null;
    }

    /**
     * 返回多行记录，并将每行记录封装到指定的类中
     * @param sql   查询语句
//     * @param clazz 封装数据的javabean类的Class对象
     * @param params    sql的参数
     * @return  查询到的结果
     */
    public <T> List<T> queryList(String sql, Class clazz, Object... params){
        Connection connection=DBManage.getConn();
        ResultSet resultSet;
        try {
            PreparedStatement ps=connection.prepareStatement(sql);
            JDBCUtils.handleParams(ps,params);
            log.info("params:【" + JSON.toJSONString(params) + "】");
            log.info("queryList:【" + sql + "】");
            resultSet=ps.executeQuery();
            List<T> result=new ArrayList();
            ResultSetMetaData resultSetMetaData= null;
            try {
                resultSetMetaData = resultSet.getMetaData();
                while (resultSet.next()){
                    Object rowObj=clazz.newInstance();
                    //获得遍历该行的字段,并将其赋值给对象
                    for(int i=0;i<resultSetMetaData.getColumnCount();i++){
                        //获取字段名
                        String columnName=resultSetMetaData.getColumnLabel(i+1);
                        //获取字段属性
                        Object columnValue=resultSet.getObject(i+1);
                        //赋值
                        invokeSet(columnName,columnValue,rowObj);
                    }
                    result.add((T) rowObj);
                }
            } catch (SQLException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            } catch (InstantiationException e) {
                e.printStackTrace();
            }finally {
                DBManage.close(ps,connection);
            }
            return result;
        } catch (SQLException e) {
            e.printStackTrace();
            return null;
        }
    }


    public static void invokeSet(String field,Object value,Object obj){
        Class clazz=obj.getClass();
        try {
            if (value != null) {
                if (value instanceof Timestamp) {
                    value = new Date(((Timestamp) value).getTime());
                }
                Method method=clazz.getDeclaredMethod("set"+StringUtils.underlineToBigCamel(field),value.getClass());
                method.invoke(obj,value);
            }
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
        }
    }

    public static void delete(Object obj){
        //通过Class获取TableInfo
        TableInfo tableInfo = TableContext.poClassTableMap(obj);
        //获得主键
        ColumnInfo columnInfo = tableInfo.getOnlyPriKey();
        String sql = "delete from " + tableInfo.getTableName() + " where " + columnInfo.getName() + " =?";
        //通过反射获取主键
        Object id = getFieldValue(StringUtils.underlineToSmallCamel(columnInfo.getName()), obj);
        executeDML(sql, new Object[]{id});
    }

    public static int executeSql(String sql,Object params){
        int res;
        try {
            PreparedStatement ps = DBManage.getConn().prepareStatement(sql);
            handleParams(ps,params);
            res = ps.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
            res = 0;
        }
        return res;
    }

}
